const { Pool } = require('pg');
// Use postgres superuser instead of financeapp_admin
const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'financeapp',
  password: 'postgres', // Default password, change if needed
  port: 5432
});

async function createMissingTables() {
  try {
    console.log('Connecting to database as postgres superuser...');
    
    // Test connection
    const connectionTest = await pool.query('SELECT NOW()');
    console.log('Database connected successfully at:', connectionTest.rows[0].now);
    
    // Create accounts table
    console.log('Creating accounts table...');
    await pool.query(`
      CREATE TABLE accounts (
        id SERIAL PRIMARY KEY,
        user_id INTEGER REFERENCES users(id) NOT NULL,
        name VARCHAR(100) NOT NULL,
        type VARCHAR(50) NOT NULL CHECK (type IN ('银行卡', '现金', '信用卡', '投资账户', '其他')),
        balance DECIMAL(12,2) DEFAULT 0.00,
        currency VARCHAR(3) DEFAULT 'CNY',
        is_active BOOLEAN DEFAULT TRUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);
    console.log('✓ Accounts table created successfully');
    
    // Create bills table
    console.log('Creating bills table...');
    await pool.query(`
      CREATE TABLE bills (
        id SERIAL PRIMARY KEY,
        user_id INTEGER REFERENCES users(id) NOT NULL,
        account_id INTEGER REFERENCES accounts(id),
        category_id INTEGER REFERENCES categories(id) NOT NULL,
        title VARCHAR(100) NOT NULL,
        amount DECIMAL(10,2) NOT NULL,
        type VARCHAR(10) CHECK (type IN ('income', 'expense')) NOT NULL,
        bill_date TIMESTAMP NOT NULL,
        description TEXT,
        payment_method VARCHAR(50),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);
    console.log('✓ Bills table created successfully');
    
    // Create ai_suggestions table
    console.log('Creating ai_suggestions table...');
    await pool.query(`
      CREATE TABLE ai_suggestions (
        id SERIAL PRIMARY KEY,
        bill_id INTEGER REFERENCES bills(id) NOT NULL,
        suggested_category VARCHAR(50),
        confidence VARCHAR(20),
        explanation TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);
    console.log('✓ AI suggestions table created successfully');
    
    // Create indexes
    console.log('Creating indexes...');
    await pool.query('CREATE INDEX IF NOT EXISTS idx_bills_account_id ON bills(account_id)');
    await pool.query('CREATE INDEX IF NOT EXISTS idx_accounts_user_id ON accounts(user_id)');
    console.log('✓ Indexes created successfully');
    
    // Grant permissions to financeapp_admin
    console.log('Granting permissions to financeapp_admin...');
    await pool.query('GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE accounts TO financeapp_admin');
    await pool.query('GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE bills TO financeapp_admin');
    await pool.query('GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE ai_suggestions TO financeapp_admin');
    console.log('✓ Permissions granted successfully');
    
    // Grant usage on sequences
    console.log('Granting sequence permissions...');
    await pool.query('GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO financeapp_admin');
    console.log('✓ Sequence permissions granted successfully');
    
    // Close pool
    await pool.end();
    console.log('Database connection closed');
    console.log('All missing tables created successfully!');
  } catch (err) {
    console.error('Error:', err.message);
    await pool.end();
  }
}

createMissingTables();